/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2007-9-30 13:20:53                           */
/*==============================================================*/

create database NCI5
go

use NCI5

/*==============================================================*/
/* Table: "Order"                                               */
/*==============================================================*/

create table "Order" (
   ord_n_ID             int              not null,
   user_n_ID            int              null,
   ord_str_ordernum     varchar(20)          null,
   ord_d_booktime       datetime             null,
   ord_d_arrivaltime    datetime             null,
   ord_n_tablenum       int               null,
   ord_n_state          int               null,
   ord_str_desc         varchar(200)         null,
   constraint PK_ORDER primary key  (ord_n_ID)
)
go

/*==============================================================*/
/* Table: Ordertableset                                         */
/*==============================================================*/
create table Ordertableset (
   ots_n_ID             int              not null,
   ord_n_ID             int             null,
   tab_n_ID             int             null,
   constraint PK_ORDERTABLESET primary key  (ots_n_ID)
)
go

/*==============================================================*/
/* Table: Restaurant                                            */
/*==============================================================*/
create table Restaurant (
   res_n_ID             int             not null,
   res_str_name         varchar(20)          null,
   res_str_address      varchar(50)          null,
   res_n_tel            int              null,
   res_n_tablenum       int               null,
   res_n_consumemin     int               null,
   res_n_consumemax     int              null,
   res_str_city         varchar(20)          null,
   res_str_desc         varchar(200)         null,
   constraint PK_RESTAURANT primary key  (res_n_ID)
)
go

/*==============================================================*/
/* Table: "Table"                                               */
/*==============================================================*/
create table "Table" (
   tab_n_ID             int              not null,
   res_n_ID             int              null,
   ta_n_ID              int              null,
   tab_n_seatnum        int              null,
   tab_n_averagecost    int              null,
   constraint PK_TABLE primary key  (tab_n_ID)
)
go

/*==============================================================*/
/* Table: Tableattribute                                        */
/*==============================================================*/
create table Tableattribute (
   ta_n_ID              int               not null,
   ta_str_name          varchar(20)          null,
   ta_str_desc          varchar(200)         null,
   constraint PK_TABLEATTRIBUTE primary key  (ta_n_ID)
)
go

/*==============================================================*/
/* Table: "User"                                                */
/*==============================================================*/
create table "User" (
   user_n_ID            int              not null,
   user_str_name        varchar(20)          null,
   user_str_pwd         varchar(20)          null,
   user_str_email       varchar(20)          null,
   user_n_tel           int              null,
   user_d_birth         datetime             null,
   user_n_creditno      int               null,
   user_str_poorrecord  int               null,
   user_n_sex           int               null,
   user_n_role          int               null,
   user_str_safequestion varchar(30)          null,
   user_str_safeanswer  varchar(30)          null,
   constraint PK_USER primary key  (user_n_ID)
)
go

alter table "Order"
   add constraint FK_ORDER_REFERENCE_USER foreign key (user_n_ID)
      references "User" (user_n_ID)
go

alter table Ordertableset
   add constraint FK_ORDERTAB_REFERENCE_ORDER foreign key (ord_n_ID)
      references "Order" (ord_n_ID)
go

alter table Ordertableset
   add constraint FK_ORDERTAB_REFERENCE_TABLE foreign key (tab_n_ID)
      references "Table" (tab_n_ID)
go

alter table "Table"
   add constraint FK_TABLE_REFERENCE_RESTAURA foreign key (res_n_ID)
      references Restaurant (res_n_ID)
go

alter table "Table"
   add constraint FK_TABLE_REFERENCE_TABLEATT foreign key (ta_n_ID)
      references Tableattribute (ta_n_ID)
go

